package com.lmonkey.servic;

import com.lmonkey.dao.Basedao;
import com.lmonkey.shop.COMMENT;
import com.lmonkey.shop.USER;

import javax.servlet.jsp.jstl.sql.Result;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class COMMENTDao {
    public static ArrayList<COMMENT> selectAll(int cp,int count,String keyword){
        ArrayList<COMMENT> list = new ArrayList<COMMENT>();
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try{
            String sql = null;
            if (keyword != null){
                sql = "select * from lmonkey_comment where comment_user like ? or comment_desc like ? order by comment_time desc limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1,"%"+keyword+"%");
                ps.setObject(2,"%"+keyword+"%");
                ps.setInt(3,(cp-1)*count);
                ps.setInt(4,count);
            }else {
                sql = "select * from lmonkey_comment order by comment_time desc limit ?,?";
                ps = conn.prepareStatement(sql);
                ps.setInt(1,(cp-1)*count);
                ps.setInt(2,count);
            }
                rs = ps.executeQuery();
                //获取用户信息
                while (rs.next()) {
                    COMMENT u = new COMMENT(
                            rs.getInt("comment_id"),
                            rs.getString("comment_user"),
                            rs.getString("comment_desc"),
                            rs.getString("comment_filename"),
                            rs.getString("comment_time"),
                            rs.getInt("comment_product_id"),
                            rs.getString("comment_p_name"),
                            rs.getFloat("comment_p_price"),
                            rs.getString("comment_p_filename")
                );
                list.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    public static int[] tital(int c,String keyword){
        //0总记录数,1总页数
        int arr[]={0,1};
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs =null;
        try {
            String sql = null;
            if (keyword != null){
                sql = "select count(*) from lmonkey_comment where comment_user like ? or comment_desc like ? ";
                ps = conn.prepareStatement(sql);
                ps.setObject(1,"%"+keyword+"%");
                ps.setObject(2,"%"+keyword+"%");
            }else {
                sql = "select count(*) from lmonkey_comment";
                ps = conn.prepareStatement(sql);
            }
            rs = ps.executeQuery();
            while(rs.next()){
                arr[0] = rs.getInt(1);
            }
            if (arr[0]%c==0){
                arr[1] = arr[0]/c;
            }else {
                arr[1] = arr[0]/c+1;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return arr;
    }

    public static int delByid(String id){
       int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "delete from lmonkey_comment where comment_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public static COMMENT selectByid(String id){
        COMMENT pl = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            String sql = "select * from lmonkey_comment where comment_id = ?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(id));
            rs = ps.executeQuery();
            while(rs.next()){
                pl = new COMMENT(
                        rs.getInt("comment_id"),
                        rs.getString("comment_user"),
                        rs.getString("comment_desc"),
                        rs.getString("comment_filename"),
                        rs.getString("comment_time"),
                        rs.getInt("comment_product_id"),
                        rs.getString("comment_p_name"),
                        rs.getFloat("comment_p_price"),
                        rs.getString("comment_p_filename")
                );
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return pl;
    }

    public  static int updateBy(String desc,int id,String file){
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try {
            String sql = null;
            if(file == null){
                sql = "update lmonkey_comment set comment_desc = ? , comment_filename = ? where comment_id = ?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1,desc);
                ps.setObject(2,file);
                ps.setInt(3,id);
            }else{
                sql = "update lmonkey_comment set comment_desc = ? , comment_filename = ? where comment_id = ?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1,desc);
                ps.setObject(2,file);
                ps.setInt(3,id);
            }
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public static ArrayList<COMMENT> selectAllid(String id){
        ArrayList<COMMENT> list = new ArrayList<COMMENT>();
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try{
                String sql = "select * from lmonkey_comment where comment_product_id = ? order by comment_time desc ";
                ps = conn.prepareStatement(sql);
                ps.setInt(1,Integer.parseInt(id));
            rs = ps.executeQuery();
            //获取用户信息
            while (rs.next()) {
                COMMENT u = new COMMENT(
                        rs.getInt("comment_id"),
                        rs.getString("comment_user"),
                        rs.getString("comment_desc"),
                        rs.getString("comment_filename"),
                        rs.getString("comment_time"),
                        rs.getInt("comment_product_id"),
                        rs.getString("comment_p_name"),
                        rs.getFloat("comment_p_price"),
                        rs.getString("comment_p_filename")
                );
                list.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static ArrayList<COMMENT> selectAll1(String userid){
        ArrayList<COMMENT> list = new ArrayList<COMMENT>();
        //声明结果集
        ResultSet rs = null;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try{
            String sql = "select * from lmonkey_comment where comment_user = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,userid);
            rs = ps.executeQuery();
            //获取用户信息
            while (rs.next()) {
                COMMENT u = new COMMENT(
                        rs.getInt("comment_id"),
                        rs.getString("comment_user"),
                        rs.getString("comment_desc"),
                        rs.getString("comment_filename"),
                        rs.getString("comment_time"),
                        rs.getInt("comment_product_id"),
                        rs.getString("comment_p_name"),
                        rs.getFloat("comment_p_price"),
                        rs.getString("comment_p_filename")
                );
                list.add(u);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public static int selectByname(String name){
        int count = 0;
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        ResultSet rs =null;
        try {
            String sql = "select count(*) from lmonkey_comment where comment_p_name = ?";
            ps = conn.prepareStatement(sql);
            ps.setObject(1,name);
            rs = ps.executeQuery();
            while(rs.next()){
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public  static int updateByid(String desc,String id){
        int count = 0;
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;

        try {
            String sql = "update lmonkey_comment set comment_desc = ? where comment_id = ?";
                ps = conn.prepareStatement(sql);
                ps.setObject(1,desc);
                ps.setInt(2,Integer.parseInt(id));
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    public static int add(String uid,String desc,String time,int pid,String pname,Float pprice,String pfile){
        //连接数据库
        Connection conn = Basedao.getconn();
        PreparedStatement ps = null;
        try {
            String sql = "insert into lmonkey_comment values(?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,0);
            ps.setObject(2,uid);
            ps.setObject(3,desc);
            ps.setObject(4,null);
            ps.setObject(5,time);
            ps.setInt(6,pid);
            ps.setObject(7,pname);
            ps.setFloat(8,pprice);
            ps.setObject(9,pfile);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
}
